﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using System.Data.Common;
using DatabaseToCode.Schema;
using System.Data.Entity.Design;
using System.Data.Entity.Design.PluralizationServices;

namespace DatabaseToCode
{
    /// <summary>
    /// 
    /// </summary>
    public class DatabaseAnalyser
    {
        /// <summary>
        /// Gets or sets the name of the provider.
        /// </summary>
        /// <value>
        /// The name of the provider.
        /// </value>
        public string ProviderName { get; set; }

        /// <summary>
        /// Gets or sets the connection string.
        /// </summary>
        /// <value>
        /// The connection string.
        /// </value>
        public string ConnectionString { get; set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="DatabaseAnalyser"/> class.
        /// </summary>
        /// <param name="providerName">Name of the provider.</param>
        /// <param name="connectionString">The connection string.</param>
        public DatabaseAnalyser(string providerName, string connectionString) {
            this.ProviderName = providerName;
            this.ConnectionString = connectionString;
        }

        /// <summary>
        /// Analyses this instance.
        /// </summary>
        /// <returns></returns>
        public Schema.Schema Analyse() {
            var s = new Schema.Schema();
            DbProviderFactory factory = DbProviderFactories.GetFactory(this.ProviderName);
            using (DbConnection connection = factory.CreateConnection()) {
                connection.ConnectionString = this.ConnectionString;
                connection.Open();

                // Récupération de la liste des Tables
                AnalyzeTables(s, connection);

                // Récupération de la liste des colonnes
                AnalyzeColumns(s, factory, connection);

                // Récupération des primary keys
                AnalyzePrimaryKeys(s, connection);

                // Récupération des foreign keys
                AnalyzeForeignKeys(s, connection);

                connection.Close();
            }
            return s;
        }

        /// <summary>
        /// Analyzes the columns.
        /// </summary>
        /// <param name="s">The s.</param>
        /// <param name="factory">The factory.</param>
        /// <param name="connection">The connection.</param>
        private static void AnalyzeColumns(Schema.Schema s, DbProviderFactory factory, DbConnection connection)
        {
            var columns = from row in connection.GetSchema("COLUMNS").AsEnumerable()
                          where s.Tables.ContainsKey(row.Field<string>("TABLE_NAME"))
                          select new
                          {
                              TableName = row.Field<string>("TABLE_NAME"),
                              ColumnName = row.Field<string>("COLUMN_NAME"),
                              DatabaseType = row.Field<string>("DATA_TYPE"),
                              MaxLength = row.Field<int?>("CHARACTER_MAXIMUM_LENGTH"),
                              AllowsNull = row.Field<string>("IS_NULLABLE").Equals("YES"),
                              //AutoIncrement = row.Field<long?>("AUTOINC_INCREMENT"),
                              //AutoIncrementSeed = row.Field<long?>("AUTOINC_SEED"),
                              Ordinal = row.Field<int>("ORDINAL_POSITION")
                          };
            columns.ToList().ForEach(c =>
            {
                s.Tables[c.TableName].Columns.Add(
                c.ColumnName,
                new Column
                {
                    Name = string.Format("[{0}]", c.ColumnName),
                    DisplayName = c.ColumnName,
                    DatabaseType = c.DatabaseType,
                    MaxLength = c.MaxLength,
                    AllowsNull = c.AllowsNull,
                    //AutoIncrement = c.AutoIncrement,
                    //AutoIncrementSeed = c.AutoIncrementSeed,
                    Ordinal = c.Ordinal
                });
                s.Tables[c.TableName].PrimaryKeyName = string.Empty;
            });

            var dataAdapter = factory.CreateDataAdapter();
            s.Tables.ToList().ForEach(
                kv =>
                {
                    var command = factory.CreateCommand();
                    command.Connection = connection;
                    command.CommandText = string.Format("SELECT TOP 5 * FROM {0} ", kv.Value.Name);
                    dataAdapter.SelectCommand = command;
                    DataTable dt = new DataTable();
                    dataAdapter.Fill(dt);

                    foreach (DataColumn c in dt.Columns)
                    {
                        s.Tables[kv.Key].Columns[c.ColumnName].IsKey = c.Unique;
                        if (c.Unique)
                        {
                            s.Tables[kv.Key].PrimaryKeyName = c.ColumnName;
                        }
                        s.Tables[kv.Key].Columns[c.ColumnName].ManagedType = c.DataType;
                    }
                }
            );
        }

        /// <summary>
        /// Analyzes the tables.
        /// </summary>
        /// <param name="s">The s.</param>
        /// <param name="connection">The connection.</param>
        private static void AnalyzeTables(Schema.Schema s, DbConnection connection)
        {
            var tables = from row in connection.GetSchema("Tables").AsEnumerable()
                         where !row.Field<string>("TABLE_TYPE").Equals("VIEW")
                         select new { TableName = row.Field<string>("TABLE_NAME") };
            tables.ToList().ForEach(r => s.Tables.Add(r.TableName, new Table { Name = string.Format("[{0}]", r.TableName), DisplayName = r.TableName }));
        }

        /// <summary>
        /// Analyzes the primary keys.
        /// </summary>
        /// <param name="s">The s.</param>
        /// <param name="connection">The connection.</param>
        private static void AnalyzePrimaryKeys(Schema.Schema s, DbConnection connection)
        {
            var dbCommand = connection.CreateCommand();
            dbCommand.CommandText = @"select 
	                tc.CONSTRAINT_NAME,
	                tc.CONSTRAINT_SCHEMA,
	                tc.TABLE_NAME as TableName,
	                kcu.COLUMN_NAME as ColumnName,
	                kcu.ORDINAL_POSITION
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
	                on kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                where tc.CONSTRAINT_TYPE='PRIMARY KEY'
                order by tc.CONSTRAINT_NAME, tc.TABLE_NAME, kcu.ORDINAL_POSITION";

            dbCommand.Connection = connection;
            try
            {
                var reader = dbCommand.ExecuteReader();
                while (reader.Read())
                {
                    s.Tables[(string)reader["TableName"]].Columns[(string)reader["ColumnName"]].IsKey = true;
                }
                reader.Close();
            }
            //catch (Exception ex) { }
            finally
            {
                dbCommand.Dispose();
            }

            s.Tables.ToList().ForEach(
                t =>
                {
                    var keys = string.Join(",", (from c in t.Value.Columns
                                                 where c.Value.IsKey
                                                 select c.Value.FieldName).ToList());
                    t.Value.PrimaryKeyName = keys;
                });
        }

        /// <summary>
        /// Analyzes the foreign keys.
        /// </summary>
        /// <param name="s">The s.</param>
        /// <param name="connection">The connection.</param>
        private static void AnalyzeForeignKeys(Schema.Schema s, DbConnection connection) {
            var dbCommand = connection.CreateCommand();
            dbCommand.CommandText = @"select 
	                tc.CONSTRAINT_NAME,
	                tc.CONSTRAINT_SCHEMA,
	                tc.TABLE_NAME,
	                kcu.COLUMN_NAME,
	                c.IS_NULLABLE,
	                kcu2.TABLE_NAME as CONSTRAINT_TABLE_NAME,
	                kcu2.COLUMN_NAME as CONSTRAINT_COLUMN_NAME, 
	                rc.DELETE_RULE
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
	                on tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
                inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
	                on rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu2
	                on rc.UNIQUE_CONSTRAINT_NAME = kcu2.CONSTRAINT_NAME
                inner join INFORMATION_SCHEMA.COLUMNS c
	                on kcu.TABLE_NAME = c.TABLE_NAME and c.COLUMN_NAME = kcu.COLUMN_NAME
                where tc.CONSTRAINT_TYPE='FOREIGN KEY'
                order by tc.CONSTRAINT_NAME, tc.TABLE_NAME, kcu.COLUMN_NAME, kcu2.TABLE_NAME, kcu2.COLUMN_NAME";
            
            dbCommand.Connection = connection;
            try
            {
                var reader = dbCommand.ExecuteReader();
                while (reader.Read())
                {
                    s.ForeignKeys.Add(
                        (string)reader["CONSTRAINT_NAME"],
                        new ForeignKey
                        {
                            Name = (string)reader["CONSTRAINT_NAME"],
                            Table = (string)reader["TABLE_NAME"],
                            Column = (string)reader["COLUMN_NAME"],
                            ConstraintTable = (string)reader["CONSTRAINT_TABLE_NAME"],
                            ConstraintColumn = (string)reader["CONSTRAINT_COLUMN_NAME"],
                            WillCascadeOnDelete = ((string)reader["DELETE_RULE"]).ToUpper().Equals("CASCADE"),
                            HasOptional = ((string)reader["IS_NULLABLE"]).ToUpper().Equals("YES")
                        }
                    );
                }
                reader.Close();
            }
            finally {
                dbCommand.Dispose();
            }
        }
    }
}
